/* This program reads in the CoreLogic housing transaction data and then assigns each transaction to a school district based on its exact longitude and latitude. It then cleans the dataset and collapses the dataset to the school district-by-year level so it can be merged with our main dataset that contains school district financial outcomes and wind turbine information. */


*Convert Shapefiles
shp2dta using "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\tl_2005_us_sde.shp", database(elementary_data) coordinates(elementary_coor)

shp2dta using "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Data for Replication\tl_2005_us_sdu.shp", database(unified_data) coordinates(unified_coor) 

*These state files are from Core Logic - we got them from LBNL. 
foreach x in AK AZ CA CO CT DE HI IA ID IL IN KS MA MD ME MI MN MO MT NC ND NE NH NJ NM NV NY OH OK OR PA RI SD TN TX UT VT WA WI WV WY {
	 
	use `x', clear
	drop _merge 
	drop if latitude == . 
	drop if longitude == .  
	
	geoinpoly latitude longitude using "D:\Capitalization\Shapefiles\elementary_coor"
	merge m:1 _ID using "D:\Capitalization\Shapefiles\elementary_data"
	drop if _merge==2
	rename NAME name_elementary 
	rename ELSDLEA ncesid_elementary
	rename GEOID geoid_elem
	drop _ID STATEFP _merge
		
	geoinpoly latitude longitude using "D:\Capitalization\Shapefiles\unified_coor"
	merge m:1 _ID using "D:\Capitalization\Shapefiles\unified_data"
	rename NAME name_unified
	rename UNSDLEA ncesid_unified
	rename GEOID geoid_unif
 	drop if _merge==2 
	
	*gen ncesid_code_str = ncesid_unified
	*replace ncesid_code_str = ncesid_elementary if ncesid_code_str == "" 
	gen ncesid_code_str = geoid_unif
	replace ncesid_code_str = geoid_elem if ncesid_code_str == "" 
	destring(ncesid_code_str), gen(ncesid_code)
	destring(fipscode), gen(t_fips)
	
	save `x'_geocoded, replace 
}

cd "D:\Capitalization\archive"

use AK_geocoded_Aug2, clear
foreach x in AZ CA CO CT DE HI IA ID IL IN KS MA MD ME MI MN MO MT NC ND NE NH NJ NM NV NY OH OK OR PA RI SD TN TX UT VT WA WI WV WY {
	append using `x'_geocoded_Aug2
}


*notes: var with "cty" ending are coutny level variables

********************************************************************************

tostring(saledate), gen(saledate2)
gen month = substr(saledate2, 5, 2)
destring(month), replace
sum month

gen quarter = . 
replace quarter = 1 if month == 1
replace quarter = 1 if month == 2
replace quarter = 1 if month == 3
replace quarter = 2 if month == 4
replace quarter = 2 if month == 5
replace quarter = 2 if month == 6
replace quarter = 3 if month == 7
replace quarter = 3 if month == 8
replace quarter = 3 if month == 9
replace quarter = 4 if month == 10
replace quarter = 4 if month == 11
replace quarter = 4 if month == 12


*2) Adjust Sale Price 
* CPI: https://data.bls.gov/cgi-bin/cpicalc.pl?cost1=1.00&year1=200510&year2=202110
gen adj_sale_price = . 
replace  adj_sale_price = (saleamount / 1.04) if saleyear == 2006 & quarter == 1
replace  adj_sale_price = (saleamount / 1.06) if saleyear == 2007 & quarter == 1
replace  adj_sale_price = (saleamount / 1.11) if saleyear == 2008 & quarter == 1
replace  adj_sale_price = (saleamount / 1.11) if saleyear == 2009 & quarter == 1
replace  adj_sale_price = (saleamount / 1.14) if saleyear == 2010 & quarter == 1
replace  adj_sale_price = (saleamount / 1.15) if saleyear == 2011 & quarter == 1
replace  adj_sale_price = (saleamount / 1.19) if saleyear == 2012 & quarter == 1
replace  adj_sale_price = (saleamount / 1.21) if saleyear == 2013 & quarter == 1
replace  adj_sale_price = (saleamount / 1.23) if saleyear == 2014 & quarter == 1
replace  adj_sale_price = (saleamount / 1.23) if saleyear == 2015 & quarter == 1
replace  adj_sale_price = (saleamount / 1.24) if saleyear == 2016 & quarter == 1
replace  adj_sale_price = (saleamount / 1.27) if saleyear == 2017 & quarter == 1
replace  adj_sale_price = (saleamount / 1.30) if saleyear == 2018 & quarter == 1
replace  adj_sale_price = (saleamount / 1.32) if saleyear == 2019 & quarter == 1
replace  adj_sale_price = (saleamount / 1.35) if saleyear == 2020 & quarter == 1
replace  adj_sale_price = (saleamount / 1.37) if saleyear == 2021 & quarter == 1


replace  adj_sale_price = (saleamount / 1.04) if saleyear == 2006 & quarter == 2
replace  adj_sale_price = (saleamount / 1.06) if saleyear == 2007 & quarter == 2
replace  adj_sale_price = (saleamount / 1.10) if saleyear == 2008 & quarter == 2
replace  adj_sale_price = (saleamount / 1.10) if saleyear == 2009 & quarter == 2
replace  adj_sale_price = (saleamount / 1.12) if saleyear == 2010 & quarter == 2
replace  adj_sale_price = (saleamount / 1.16) if saleyear == 2011 & quarter == 2
replace  adj_sale_price = (saleamount / 1.18) if saleyear == 2012 & quarter == 2
replace  adj_sale_price = (saleamount / 1.19) if saleyear == 2013 & quarter == 2
replace  adj_sale_price = (saleamount / 1.22) if saleyear == 2014 & quarter == 2
replace  adj_sale_price = (saleamount / 1.22) if saleyear == 2015 & quarter == 2
replace  adj_sale_price = (saleamount / 1.23) if saleyear == 2016 & quarter == 2
replace  adj_sale_price = (saleamount / 1.26) if saleyear == 2017 & quarter == 2
replace  adj_sale_price = (saleamount / 1.29) if saleyear == 2018 & quarter == 2
replace  adj_sale_price = (saleamount / 1.31) if saleyear == 2019 & quarter == 2
replace  adj_sale_price = (saleamount / 1.32) if saleyear == 2020 & quarter == 2
replace  adj_sale_price = (saleamount / 1.37) if saleyear == 2021 & quarter == 2

replace  adj_sale_price = (saleamount / 1.04) if saleyear == 2006 & quarter == 3
replace  adj_sale_price = (saleamount / 1.06) if saleyear == 2007 & quarter == 3
replace  adj_sale_price = (saleamount / 1.10) if saleyear == 2008 & quarter == 3
replace  adj_sale_price = (saleamount / 1.10) if saleyear == 2009 & quarter == 3
replace  adj_sale_price = (saleamount / 1.12) if saleyear == 2010 & quarter == 3
replace  adj_sale_price = (saleamount / 1.16) if saleyear == 2011 & quarter == 3
replace  adj_sale_price = (saleamount / 1.17) if saleyear == 2012 & quarter == 3
replace  adj_sale_price = (saleamount / 1.20) if saleyear == 2013 & quarter == 3
replace  adj_sale_price = (saleamount / 1.22) if saleyear == 2014 & quarter == 3
replace  adj_sale_price = (saleamount / 1.22) if saleyear == 2015 & quarter == 3
replace  adj_sale_price = (saleamount / 1.23) if saleyear == 2016 & quarter == 3
replace  adj_sale_price = (saleamount / 1.25) if saleyear == 2017 & quarter == 3
replace  adj_sale_price = (saleamount / 1.29) if saleyear == 2018 & quarter == 3
replace  adj_sale_price = (saleamount / 1.31) if saleyear == 2019 & quarter == 3
replace  adj_sale_price = (saleamount / 1.33) if saleyear == 2020 & quarter == 3
replace  adj_sale_price = (saleamount / 1.37) if saleyear == 2021 & quarter == 3

replace  adj_sale_price = (saleamount / 1.01) if saleyear == 2006 & quarter == 4
replace  adj_sale_price = (saleamount / 1.05) if saleyear == 2007 & quarter == 4
replace  adj_sale_price = (saleamount / 1.09) if saleyear == 2008 & quarter == 4
replace  adj_sale_price = (saleamount / 1.09) if saleyear == 2009 & quarter == 4
replace  adj_sale_price = (saleamount / 1.10) if saleyear == 2010 & quarter == 4
replace  adj_sale_price = (saleamount / 1.14) if saleyear == 2011 & quarter == 4
replace  adj_sale_price = (saleamount / 1.16) if saleyear == 2012 & quarter == 4
replace  adj_sale_price = (saleamount / 1.17) if saleyear == 2013 & quarter == 4
replace  adj_sale_price = (saleamount / 1.19) if saleyear == 2014 & quarter == 4
replace  adj_sale_price = (saleamount / 1.19) if saleyear == 2015 & quarter == 4
replace  adj_sale_price = (saleamount / 1.21) if saleyear == 2016 & quarter == 4
replace  adj_sale_price = (saleamount / 1.24) if saleyear == 2017 & quarter == 4
replace  adj_sale_price = (saleamount / 1.27) if saleyear == 2018 & quarter == 4
replace  adj_sale_price = (saleamount / 1.29) if saleyear == 2019 & quarter == 4
replace  adj_sale_price = (saleamount / 1.31) if saleyear == 2020 & quarter == 4
replace  adj_sale_price = (saleamount / 1.39) if saleyear == 2021 & quarter == 4

replace  adj_sale_price = saleamount if saleyear == 2005

drop if saleyear < 2005

egen garagecode2 = group(garagecode)

foreach z in garagecode2 bedroomsallbuildings fullbathsallbuildings livingsquarefeetallbuildings yearbuilt { 
	
	gen `z'2 = `z' if `z'!=. 
	gen `z'X = `z' == . 
	bysort mailingcity: egen m`z' = mean(`z')
	replace `z'2 = m`z' if `z' == . 
	
}
drop if adj_sale_price == .
gen ln_adj_price = ln(adj_sale_price)

save "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\working_file.dta", replace


***********************
***********************

gen ncesid = ncesid_code

gen unique_id = _n

drop _merge

geonear unique_id parcellevellatitude parcellevellongitude using "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\uswtdbCSV (3)\turbine_for_geocoding.dta", n(case_id ylat xlong) near(1) mi

save "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", replace

********************************************************************************
********************************************************************************

use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
merge m:1 ncesid using "C:\Users\schwegma\Dropbox\PC (3)\Downloads\treated_districts.dta"
drop if _merge == 2
keep if _merge==3
gen transaction = 1 
keep if mi_to_nid <= 2 
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
tempfile collapse_within2_treated
save `collapse_within2_treated', replace

use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
merge m:1 ncesid using "C:\Users\schwegma\Dropbox\PC (3)\Downloads\treated_districts.dta"
drop if _merge == 2
keep if _merge==1
gen transaction = 1 
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
append using `collapse_within2_treated'
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_within2_aug14", replace


use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
merge m:1 ncesid using "C:\Users\schwegma\Dropbox\PC (3)\Downloads\treated_districts.dta"
drop if _merge == 2
keep if _merge==3
gen transaction = 1 
keep if mi_to_nid <= 1
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
tempfile collapse_within1_treated
save `collapse_within1_treated', replace

use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
merge m:1 ncesid using "C:\Users\schwegma\Dropbox\PC (3)\Downloads\treated_districts.dta"
drop if _merge == 2
keep if _merge==1
gen transaction = 1 
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
append using `collapse_within1_treated'
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_within1_setp25", replace


use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
gen transaction = 1 
gen dropvar = mi_to_nid >= 2 & mi_to_nid <= 10
sum mi_to_nid if dropvar == 1 
drop if dropvar == 1  
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_within2_beyond10", replace


use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
gen transaction = 1 
gen dropvar = mi_to_nid >= 1 & mi_to_nid <= 5
sum mi_to_nid if dropvar == 1 
drop if dropvar == 1  
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_within1_beyond5", replace

use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
gen transaction = 1 
gen dropvar = mi_to_nid >= 1 & mi_to_nid <= 10
sum mi_to_nid if dropvar == 1 
drop if dropvar == 1  
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_within1_beyond10", replace


use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
gen transaction = 1 
drop if mi_to_nid <= 2
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_over2_updated", replace


****Keep 
use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
drop if mi_to_nid <= 2
keep ncesid 
duplicates drop
save "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\school_districts_within2_miles", replace  

use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
gen transaction = 1
drop if mi_to_nid <= 1
merge m:1 ncesid using "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\school_districts_within2_miles" 
keep if _merge==3 
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_over1_updated_static", replace


use "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\full_analytic_file_aug14", clear 
gen transaction = 1
drop if mi_to_nid <= 1.5
merge m:1 ncesid using "C:\Users\schwegma\Dropbox\Berkeley Solar Energy\New Capitalization Work - Project 2\Fiscal\school_districts_within2_miles" 
keep if _merge==3 
collapse (mean) adj_sale_price ln_adj_price yearbuilt2 livingsquarefeetallbuildings2 fullbathsallbuildings2 bedroomsallbuildings2 garagecode2X (sum) transaction, by(ncesid saleyear)
save "C:\Users\schwegma\Dropbox\Collapsed Transaction Data (CoreLogic Data)\school_year_collapse_over1_half_updated_static", replace

